package com.zhu.gmallpublisher.mapper;

import com.zhu.gmallpublisher.bean.CategoryCommodityStats;
import com.zhu.gmallpublisher.bean.TradeMarkCommodityStats;
import com.zhu.gmallpublisher.bean.TradeMarkOrderAmountPieGraph;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 商品主题
 *
 * full outer join
 */
public interface CommodityStatsMapper {


    /**
     *各个品牌交易分析   订单人数金额  退单人数金额   全外连
     * @param date
     * @return
     */
    @Select("select trademark_name,order_count,order_amount,refund_count," +
            "refund_uu_count from(" +
            " select trademark_id,trade_name,sum(distinct user_id) as uu_count," +
            "sum(order_amount) order_amount " +
            "from dws_trade_trademark_category_user_spu_order_window " +
            "where toYYYYMMDD(stt) = #{date} group by trademark_id,trademark_name) oct" +
            " full outer join (" +
            "select trademark_id,trademark_name,sum(refund_count) refund_count," +
            "count(distinct user_id) refund_uu_count " +
            "from dws_trade_trademark_category_user_refund_window " +
            "where toYYYYMMDD(stt) = #{date} group by trademark_id,trademark_name) rct " +
            "on oct.trademark_id = rct.trademark_id; ")
    List<TradeMarkCommodityStats> getTradeMarkCommodityStats(@Param("date") Integer date);


    /**
     * 商品主题交易 分析  饼状图  只显示品牌金额
     * @param date
     * @return
     */
    @Select("select trademark_name,sum(order_amount) as order_amount " +
    "from dws_trade_trademark_category_user_spu_order_window " +
            "where toYYYYMMDD(stt) = #{date} group by trademark_id,trademark_name ")
    List<TradeMarkOrderAmountPieGraph> selectTmOrderAmtPieGra(@Param("date") Integer date);


    /**
     * 商品主题  各品类商品交易分析 字段 cate123  ordercount orderamount orderrefund  orderAmount
     */
    @Select("select category1_name,\n" +
            "       category2_name,\n" +
            "       category3_name,\n" +
            "       order_count,\n" +
            "       uu_count,\n" +
            "       order_amount,\n" +
            "       refund_count,\n" +
            "       refund_uu_count\n" +
            "from (select category1_id,\n" +
            "             category1_name,\n" +
            "             category2_id,\n" +
            "             category2_name,\n" +
            "             category3_id,\n" +
            "             category3_name,\n" +
            "             sum(order_count)        order_count,\n" +
            "             count(distinct user_id) uu_count,\n" +
            "             sum(order_amount)       order_amount\n" +
            "      from dws_trade_trademark_category_user_spu_order_window\n" +
            "      where toYYYYMMDD(stt) = #{date}\n" +
            "      group by category1_id,\n" +
            "               category1_name,\n" +
            "               category2_id,\n" +
            "               category2_name,\n" +
            "               category3_id,\n" +
            "               category3_name) oct\n" +
            "         full outer join\n" +
            "     (select category1_id,\n" +
            "             category1_name,\n" +
            "             category2_id,\n" +
            "             category2_name,\n" +
            "             category3_id,\n" +
            "             category3_name,\n" +
            "             sum(refund_count)       refund_count,\n" +
            "             count(distinct user_id) refund_uu_count\n" +
            "      from dws_trade_trademark_category_user_refund_window\n" +
            "      where toYYYYMMDD(stt) = #{date}\n" +
            "      group by category1_id,\n" +
            "               category1_name,\n" +
            "               category2_id,\n" +
            "               category2_name,\n" +
            "               category3_id,\n" +
            "               category3_name) rct\n" +
            "     on oct.category1_id = rct.category1_id\n" +
            "         and oct.category2_id = rct.category2_id\n" +
            "         and oct.category3_id = rct.category3_id;")
    List<CategoryCommodityStats> selectCategoryCommodityStats(@Param("date") Integer date);


}
